import pandas as pd
from functools import reduce

# 定义文件路径
file_paths = [
    'D:/project-2024-a/data/ERP_CLXX.csv',
    'D:/project-2024-a/data/ERP_FHDXX.csv',
    'D:/project-2024-a/data/ERP_FHJL.csv',
    'D:/project-2024-a/data/ERP_KHXD.csv',
    'D:/project-2024-a/data/ERP_KHXX.csv',
    'D:/project-2024-a/data/ERP_ZDXX.csv',
    'D:/project-2024-a/data/sys_dict_data.csv',
    'D:/project-2024-a/data/sys_user.csv'
]


# 读取CSV文件并添加前缀
dfs = []
for i, file in enumerate(file_paths):
    try:
        # 读取CSV文件
        df = pd.read_csv(file, encoding='utf-8-sig')

        # 如果是第8个文件，将user_id重命名为id
        if i == 7:  # 注意索引从0开始，所以第8个文件的索引是7
            df.rename(columns={'user_id': 'id'}, inplace=True)
        else:
            # 添加前缀
            df = df.add_prefix(f'file{i + 1}_')
            # 修改id列名称
            df.rename(columns={f'file{i + 1}_id': 'id'}, inplace=True)

        # 添加到列表中
        dfs.append(df)

    except Exception as e:
        print(f"Error reading {file}: {e}")

# 检查所有DataFrame是否包含'id'列
for i, df in enumerate(dfs):
    if 'id' not in df.columns:
        print(f"DataFrame from {file_paths[i]} does not contain 'id' column")
        raise KeyError(f"'id' column not found in {file_paths[i]}")

# 合并所有DataFrame，按id列
df_merged = reduce(lambda left, right: pd.merge(left, right, on='id', how='outer'), dfs)

# 按照id列进行排序
df_merged = df_merged.sort_values(by='id').reset_index(drop=True)

# 将合并后的数据写入新的CSV文件
df_merged.to_csv('merged_file.csv', index=False, encoding='utf-8-sig')